/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Dao;

import Entidad.*;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.AbstractList;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author ANDREZ
 */
public class NotaDao {
       
    private static NotaDao INSTANCE = new NotaDao();
    private NotaDao() {
    }
    public static NotaDao getInstance(){
    return INSTANCE;
    }
    
   public List<NotaBE.TipoNotaBE> ListarTipoNota(int idtipnota)
    {
        List<NotaBE.TipoNotaBE> lista = new ArrayList();
        String sql ="{call ListarTipoNota(?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        ResultSet rs;
        try {
            cs = c.prepareCall(sql);
            cs.setInt(1, idtipnota);
            rs = cs.executeQuery();
            while(rs.next())
            {
                  NotaBE cc = new NotaBE();
                  NotaBE.TipoNotaBE obj = cc.new TipoNotaBE();
                obj.setId(rs.getInt("IdNota"));
                obj.setNombre(rs.getString("Descripcion"));
                obj.setIdparent(rs.getInt("IdParent"));
               lista.add(obj);
            }
        } catch (SQLException ex) {
            Logger.getLogger(NotaDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return  lista;
    }
   
   public boolean IngresarNotas(int idnota,int idtiponota,int idtipo, int nota)
    {
       Boolean esCorrrecto =false;
        String sql ="{call Notas_Ingrear_pa(?,?,?,?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        try {
            cs = c.prepareCall(sql);
            cs.setInt(1, idnota);
            cs.setInt(2, idtiponota);
            cs.setInt(3, idtipo);
            cs.setInt(4, nota);
            esCorrrecto = cs.execute();
            esCorrrecto =true;
           
        } catch (SQLException ex) {
            Logger.getLogger(NotaDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return  esCorrrecto;
    }
   
   public List<CursoBE> ListarCursos(int idseccion,int idgrado, int anoelectivo)
    {
        List<CursoBE> lista = new ArrayList();
        CursoBE obj = null;
        String sql ="{call Cursos_ListarCursosPorSeccion(?,?,?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        ResultSet rs;
        try {
            cs = c.prepareCall(sql);
            cs.setInt(1, idseccion);
            cs.setInt(2, idgrado);
            cs.setInt(3, anoelectivo);
            rs = cs.executeQuery();
            while(rs.next())
            {
                obj = new CursoBE();
                obj.setId(rs.getInt("IdCurso"));
                obj.setNombre(rs.getString("NombreCurso"));
                obj.setDescripcion(rs.getString("Apellidos") +"," +rs.getString("Nombres"));
               lista.add(obj);
            }
        } catch (SQLException ex) {
            Logger.getLogger(NotaDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return  lista;
    }
    
    public List<ClaseBE> ListarCursosPorEmpleado(int idEmpleado, int anolectivo)
    {
        List<ClaseBE> lista = new ArrayList();
        String sql ="{call Cursos_ListarCursosPorEmpleado(?,?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        ResultSet rs;
        try {
            cs = c.prepareCall(sql);
            cs.setInt(1, idEmpleado);
            cs.setInt(2, anolectivo);
            rs = cs.executeQuery();
            while(rs.next())
            {
                
                  ClaseBE cc = new ClaseBE();
                  //NotaBE.TipoNotaBE obj = cc.new TipoNotaBE();
                cc.setId(rs.getInt("IdClase"));
                cc.setCodigoclase(rs.getString("CodigoClase"));
                CursoBE cur = new CursoBE();
                cur.setId(rs.getInt("IdCurso"));
                cur.setNombre(rs.getString("Nombre"));
                cc.setCurso(cur);
                GradoBE grado = new GradoBE();
                grado.setId(rs.getInt("IdGrado"));
                grado.setGrado(rs.getInt("Grado"));
                BaseBE Nivel = new BaseBE();
                Nivel.setId(rs.getInt("IdTipoNivel"));
                Nivel.setNombre(rs.getString("Descripcion"));
                grado.setTiponivel(Nivel);
                BaseBE seccion = new BaseBE();
                seccion.setId(rs.getInt("IdSeccion"));
                seccion.setNombre(rs.getString("Seccion"));
                cc.setGrado(grado);
                cc.setSeccion(seccion);
                
               lista.add(cc);
            }
        } catch (SQLException ex) {
            Logger.getLogger(NotaDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return  lista;
    }
    
    
    public List<AlumnoBE> ListarNotasPorCurso(int idcurso,int idseccion)
    {
       List<AlumnoBE> cc = null;
        String sql ="{call Notas_ListarNotasPorCurso_pa(?,?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        ResultSet rs;
         AlumnoBE getmp = null;
 List<NotaBE.DetalleNotaBE> listade = new ArrayList();
         AlumnoBE ge = null;
 
         boolean EsPrimero = true;
        try {
            cs = c.prepareCall(sql);
            cs.setInt(1, idcurso);
            cs.setInt(2, idseccion);
            rs = cs.executeQuery();
             cc = new ArrayList();
            while(rs.next())
            {  
                
                if(getmp==null || getmp.getId()!=rs.getInt("IdAlumno")){
                    if(EsPrimero==false)
                    {
                      cc.add(ge);
                    }
                    
               ge =  new AlumnoBE();
                ge.setId(rs.getInt("IdAlumno"));
                ge.setApellidos(rs.getString("Apellidos"));
                ge.setNombrepersona(rs.getString("Nombres"));
                listade = new ArrayList();
                 NotaBE de = new NotaBE();
                  NotaBE.DetalleNotaBE obj = de.new DetalleNotaBE();
                  obj.setNota(rs.getInt("Nota"));
                   NotaBE tip = new NotaBE();
                  NotaBE.TipoNotaBE tipo = tip.new TipoNotaBE();
                  tipo.setId(rs.getInt("IdTipo"));
                  obj.setTipo(tipo);
                  listade.add(obj);
                     NotaBE nota = new NotaBE();
                     nota.setId(rs.getInt("IdNota"));
                nota.setNotafinal(rs.getInt("NotaFinal"));
                nota.setDetallenota(listade);
                ge.setNota(nota);
                EsPrimero=false;
                getmp=ge;
                }else
                {
                 NotaBE de = new NotaBE();
                  NotaBE.DetalleNotaBE obj = de.new DetalleNotaBE();
                  obj.setNota(rs.getInt("Nota"));
                   NotaBE tip = new NotaBE();
                  NotaBE.TipoNotaBE tipo = tip.new TipoNotaBE();
                  tipo.setId(rs.getInt("IdTipo"));
                  obj.setTipo(tipo);
                  listade.add(obj);
                   getmp=ge;
                
                }
              
            }
            if(ge!=null && ge.getApellidos()!=null)
            cc.add(ge);
        
           
                  
          
        } catch (SQLException ex) {
            Logger.getLogger(NotaDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return  cc;
    }
    
}
